package org.syh.boyangfighthealthclub.dao.impl;

import org.syh.boyangfighthealthclub.dao.IBookDao;
import org.syh.boyangfighthealthclub.entity.Book;
import org.syh.boyangfighthealthclub.utils.JavaDateConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: Ben
 * @Date: 2021/3/12 10:10
 */
public class BookDaoImpl implements IBookDao {
    @Override
    public boolean insertBook(Connection connection, Book book) {
        String sql = "insert into boxing_book (bookname,publishname,author,bookdescription,isBorrow) values(?,?,?,?,?);";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, book.getBookName());
            preparedStatement.setString(2, book.getPublishName());
            preparedStatement.setString(3, book.getAuthor());
            preparedStatement.setString(4, book.getBookDescription());
            preparedStatement.setString(5,book.getIsBorrow());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.out.println("图书添加失败");
            e.printStackTrace();
            return false;
        }finally{
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                System.out.println("资源关闭失败");
                e.printStackTrace();
                return false;
            } finally {
                JavaDateConnection.closeCon(connection);
                return true;
            }
        }
    }

    @Override
    public boolean deleteBook(Connection connection, Integer id) {
        String sql = "delete from boxing_book"+" where id = ?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.out.println("图书删除失败");
            e.printStackTrace();
            return false;
        }finally{
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                System.out.println("资源关闭失败");
                e.printStackTrace();
                return false;
            } finally {
                JavaDateConnection.closeCon(connection);
            }
        }
        return true;
    }

    @Override
    public boolean updateBook(Connection connection, Book book) {
        String sql = "update boxing_book" + " set bookname= ?, publishname = ?, author = ?,  bookdescription = ?, isBorrow = ?" + " where id= ?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, book.getBookName());
            preparedStatement.setString(2, book.getPublishName());
            preparedStatement.setString(3, book.getAuthor());
            preparedStatement.setString(4, book.getBookDescription());
            preparedStatement.setString(5,book.getIsBorrow());
            preparedStatement.setInt(6, book.getId());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.out.println("图书更改失败");
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                System.out.println("资源关闭失败");
                e.printStackTrace();
                return false;
            } finally {
                JavaDateConnection.closeCon(connection);
            }
            return true;
        }
    }

    @Override
    public List<Book> selectBooks(Connection connection, int pageNumber, int pageSize) {
        List<Book> bookList = null;
        String sql = "select * from boxing_book order by id limit ?,?";
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            bookList=new ArrayList<>();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, (pageNumber - 1) * pageSize);
            preparedStatement.setInt(2, pageSize);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                bookList.add(new Book(resultSet.getInt("id"),
                        resultSet.getString("bookname"), resultSet.getString("publishname"),
                        resultSet.getString("author"), resultSet.getString("bookdescription"),resultSet.getString("isBorrow")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    JavaDateConnection.closeCon(connection);
                    return bookList;
                }
            }
        }
    }

    @Override
    public int selectBook(Connection connection) {
        int count = 0;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        String sql = "select count(*) from boxing_book";
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery(sql);
            if (resultSet.next()) {
                count = resultSet.getInt(1);
                return count;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    JavaDateConnection.closeCon(connection);
                }
            }
        }
        return count;
    }

    @Override
    public Book selectBookById(Connection connection, Integer id) {
        ResultSet resultSet = null;
        String sql = "select * from boxing_book where id=?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
              return  new Book(resultSet.getInt("id"),
                        resultSet.getString("bookname"), resultSet.getString("publishname"),
                        resultSet.getString("author"), resultSet.getString("bookdescription"),resultSet.getString("isBorrow"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    JavaDateConnection.closeCon(connection);
                }
            }
        }
        return null;
    }


    @Override
    public Book selectBookByBookName(Connection connection, String bookName) {
        ResultSet resultSet = null;
        String sql = "select * from boxing_book where bookname=?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, bookName);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return  new Book(resultSet.getInt("id"),
                        resultSet.getString("bookname"), resultSet.getString("publishname"),
                        resultSet.getString("author"), resultSet.getString("bookdescription"),resultSet.getString("isBorrow"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    JavaDateConnection.closeCon(connection);
                }
            }
        }
        return null;
    }
}
